Imports System.Data

''' <summary>
''' Microsoft SQL Server (and MSDE, Express, etc.)
''' </summary>
''' <remarks>The default administrative login is sa/(no default pswd)</remarks>
Public Class clsDBSQLServer
    Inherits clsDB

    ''' <summary>
    ''' Return a copy of the current generic connection
    ''' </summary>
    ''' <returns>Clone of current instance</returns>
    Public Overrides Function Clone() As clsDB
        Dim db As clsDB = New clsDBSQLServer
        MyBase.CloneSelf(db)
        Return db
    End Function

    ''' <summary>
    ''' Provider-independent access to CommandBuilder object
    ''' </summary>
    ''' <param name="da">Data adapter</param>
    ''' <returns>CommandBuilder object specific to provider type</returns>
    Public Overrides Function CommandBuilder(ByVal da As DbDataAdapter) As DbCommandBuilder
        Return New SqlClient.SqlCommandBuilder(da)
    End Function

    Public Overrides Sub CreateAllCommands(db_da As System.Data.Common.DbDataAdapter)
        MyBase.CreateAllCommands(db_da)
        Dim cb As New SqlClient.SqlCommandBuilder(db_da)
        db_da.InsertCommand = cb.GetInsertCommand.Clone
        db_da.UpdateCommand = cb.GetUpdateCommand.Clone
        db_da.DeleteCommand = cb.GetDeleteCommand.Clone
        cb.Dispose()
        cb = Nothing
    End Sub
    ''' <summary>
    ''' Return a data adapter for the current connection containing data from sql string
    ''' </summary>
    ''' <param name="SqlString">standard sql string</param>
    ''' <returns>Data Adapter</returns>
    Public Overrides Function DataAdapter(ByVal SqlString As String) As DbDataAdapter
        If Connection Is Nothing Then Return Nothing
        Return New SqlClient.SqlDataAdapter(SqlString, CType(Connection, SqlClient.SqlConnection))
    End Function

    ''' <summary>
    ''' Create a parameter (used to speed up queries and updates); value is initialized to dbnull
    ''' </summary>
    ''' <param name="ParameterName">name of parameter (usually field name)</param>
    ''' <param name="DataType">need to work on this to standarize</param>
    ''' <param name="Size">size if appropriate (strings)</param>
    ''' <returns>Data Parameter</returns>
    Public Overrides Function Parameter(ByRef Command As DbCommand, ByVal ParameterName As String, ByVal DataType As System.Type, Optional ByVal Size As Int16 = 0) As DbParameter
        If Connection Is Nothing Then Return Nothing
        Dim param As SqlClient.SqlParameter
        Dim SqlType As SqlDbType
        If DataType Is GetType(String) Then
            SqlType = SqlDbType.VarChar
        ElseIf DataType Is GetType(Date) Then
            SqlType = SqlDbType.DateTime
        ElseIf DataType Is GetType(Double) Then
            SqlType = SqlDbType.Float
        ElseIf DataType Is GetType(Integer) Then
            SqlType = SqlDbType.Int
        ElseIf DataType Is GetType(Boolean) Then
            SqlType = SqlDbType.Bit
        ElseIf DataType Is GetType(Short) Then
            SqlType = SqlDbType.SmallInt
        Else
            Throw New ApplicationException("Invalid type passed to Parameter")
            Return Nothing
        End If
        'If Size > 0 Then
        '    param = New SqlClient.SqlParameter(ParameterName, SqlType, Size)
        'Else
        '    param = New SqlClient.SqlParameter(ParameterName, SqlType)
        'End If
        'param.Value = DBNull.Value
        param = Command.CreateParameter
        With param
            .ParameterName = ParameterName
            .SqlDbType = SqlType
            If Size > 0 Then .Size = Size
            .Value = DBNull.Value
        End With
        Return param
    End Function

    ''' <summary>
    ''' Creates a provider-specific connection object given previously specified properties as required (DataSource, DatabaseName, UserName, Password, etc.)
    ''' Note that the connection is normally automatically created when you instantiate the class
    ''' </summary>
    Public Overrides Sub CreateConnection()
        Try
            Dim csb As New SqlClient.SqlConnectionStringBuilder
            With csb
                .DataSource = DataSource
                .InitialCatalog = DatabaseName
                .IntegratedSecurity = (UserName = "")
                If Not .IntegratedSecurity Then
                    .UserID = UserName
                    .Password = Password
                End If
                .ConnectTimeout = 5
                Connection = New SqlClient.SqlConnection(.ConnectionString)
            End With
        Catch ex As Exception
            Throw
            Connection = Nothing
        End Try
    End Sub

    ''' <summary>
    ''' Creates new database on the provider (uses supervisor access)
    ''' </summary>
    ''' <param name="DatabaseName">Desired name of new database</param>
    ''' <returns>True if successful</returns>
    Public Overrides Function CreateDatabase(ByRef DatabaseName As String) As Boolean
        Dim csb As New SqlClient.SqlConnectionStringBuilder
        With csb
            .DataSource = DataSource
            .InitialCatalog = "master"
            .IntegratedSecurity = True
            '.UserID = "sa"
            '.Password = ""
            Dim cn As New SqlClient.SqlConnection(.ConnectionString)
            With New SqlClient.SqlCommand("CREATE DATABASE " & DatabaseName, cn)
                Try
                    cn.Open()
                    .ExecuteNonQuery()
                    Return True
                Catch ex As Exception
                    Throw New ApplicationException("Unable to create SQL Server database: " & DatabaseName, ex)
                    Return False
                Finally
                    .Dispose()
                    cn.Close()
                    cn.Dispose()
                End Try
            End With
        End With
    End Function

    Public Sub test()

    End Sub

End Class
